package com.easylinkin.linkappapi.common.utils.excel;


import com.easylinkin.linkappapi.common.constant.CommonConstant;
import com.easylinkin.linkappapi.common.exceptions.BusinessException;
import com.easylinkin.linkappapi.common.exceptions.ExceptionEnum.CommonExceptionEnum;
import com.easylinkin.linkappapi.common.utils.StringHelper;
import com.easylinkin.linkappapi.common.utils.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;

import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p></p>
 *
 * @since 2020/4/10 8:54
 */
public class ExcelTools implements Serializable {

    private static final long serialVersionUID = 1L;

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelTools.class);

    /**
     * readXlsPart:(根据传进来的map集合读取Excel) 传进来4个参数 <String,String>类型，第二个要反射的类的具体路径)
     *
     * @param is 文件输入流
     * @param map 表头和属性的Map集合,其中Map中Key为Excel列的名称，Value为反射类的属性
     * @param clazz 需要映射的model的clazz
     * @author likaixuan
     * @since JDK 1.7
     */
    public static <T> List<T> readExcel(InputStream is, String fileType, Map<String, String> map,
        Class<T> clazz, int... rowNumIndex)
        throws Exception {
        //返回键的集合
        T obj;
        List<Object> list = new ArrayList<>();
        Workbook wb;
        wb = getWorkBook(fileType, is);
        if (null == wb) {
            throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, "读取文件异常");
        }
        // 获取每个Sheet表
        for (int sheetNum = 0; sheetNum < 1; sheetNum++) {
            // 记录第x行为表头
            int rowNumX = -1;
            // 存放每一个field字段对应所在的列的序号
            Map<String, Integer> cellMap = new HashMap<>(
                CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
            // 存放所有的表头字段信息
            List<String> headList = new ArrayList();
            Sheet sheet = wb.getSheetAt(sheetNum);
            // 设置默认最大行为2w行
            if (sheet != null && sheet.getLastRowNum() > 60000) {
                throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR,
                    "Excel 数据超过60000行,请检查是否有空行,或分批导入");
            } else {
                if (null == sheet || sheet.getLastRowNum() == 0) {
                    throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, "Excel请检查是否有空行");
                }
                // 循环行Row
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    // 如果传值指定从第几行开始读，就从指定行寻找，否则自动寻找
                    if (rowNumIndex != null && rowNumIndex.length > 0 && rowNumX == -1) {
                        Row row = sheet.getRow(rowNumIndex[0]);
                        if (row == null) {
                            throw new BusinessException(CommonExceptionEnum.NULL_EXCEPTION_ERROR,
                                "指定的行为空，请检查");
                        }
                        rowNum = rowNumIndex[0] - 1;
                    }
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    boolean flag = false;
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        if (row.getCell(i) != null && !("")
                            .equals(row.getCell(i).toString().trim())) {
                            flag = true;
                        }
                    }
                    if (!flag) {
                        continue;
                    }
                    if (rowNumX == -1) {
                        // 循环列Cell
                        for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            if (cell == null) {
                                continue;
                            }
                            String tempCellValue = sheet.getRow(rowNum).getCell(cellNum)
                                .getStringCellValue();
                            tempCellValue = StringUtils.remove(tempCellValue, (char) 160);
                            tempCellValue = tempCellValue.trim();
                            headList.add(tempCellValue);
                            for (Map.Entry<String, String> entry : map.entrySet()) {
                                String key = entry.getKey();
                                String value = entry.getValue();
                                if (StringUtils.isNotBlank(tempCellValue) && StringUtils
                                    .equals(tempCellValue, key)) {
                                    rowNumX = rowNum;
                                    String attr = value;
                                    cellMap.put(attr, cellNum);
                                }
                            }
                            if (rowNumX == -1) {
                                throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR,
                                    "没有找到对应的字段或者对应字段行上面含有不为空白的行字段");
                            }
                        }
                    } else {
                        obj = clazz.newInstance();
                        for (Map.Entry<String, String> entry : map.entrySet()) {
                            String key = entry.getKey();
                            String value = entry.getValue();
                            Integer cellNumX = cellMap.get(value);
                            if (cellNumX == null || row.getCell(cellNumX) == null) {
                                continue;
                            }
                            // 得到属性
                            String attr = value;
                            Class[] beanClasses = {obj.getClass()};
                            Class<?> attrType = BeanUtils.findPropertyType(attr, beanClasses);
                            Cell cell = row.getCell(cellNumX);
                            getValue(cell, obj, attr, attrType, rowNum, cellNumX, key);

                        }
                        list.add(obj);
                    }
                }
            }
        }
        is.close();
        wb.close();
        return (List<T>) list;
    }

    /**
     * readXlsPart:(根据传进来的map集合读取Excel) 传进来4个参数 <String,String>类型，第二个要反射的类的具体路径)
     *
     * @param is 文件输入流
     * @param map 表头和属性的Map集合,其中Map中Key为Excel列的名称，Value为反射类的属性
     * @param clazz 需要映射的model的clazz
     * @author likaixuan
     * @since JDK 1.7
     */
    public static <T> Map readExcelV1(InputStream is, String fileType, Map<String, String> map,
        Class<T> clazz, int... rowNumIndex)
        throws Exception {
        //返回键的集合
        T obj;
        List<Object> list = new ArrayList<>();
        Workbook wb;
        wb = getWorkBook(fileType, is);
        if (null == wb) {
            throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, "读取文件异常");
        }
        Map result = new HashMap();
        List<String> cellList = new ArrayList<>();
        cellList.add("广交会编码");
        cellList.add("企业中文名称");
        cellList.add("展区");
        cellList.add("特装推荐数");
        cellList.add("标摊推荐数");
        cellList.add("统一布展推荐数");
        cellList.add("中央通道推荐数");
        cellList.add("交易团推荐粘连数");
        // 获取每个Sheet表
        for (int sheetNum = 0; sheetNum < 1; sheetNum++) {
            // 记录第x行为表头
            int rowNumX = -1;
            // 存放每一个field字段对应所在的列的序号
            Map<String, Integer> cellMap = new HashMap<>(
                CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
            // 存放所有的表头字段信息
            List<String> headList = new ArrayList();
            Sheet sheet = wb.getSheetAt(sheetNum);
            // 设置默认最大行为2w行
            if (sheet != null && sheet.getLastRowNum() > 60000) {
                result.put("status", "0");
                result.put("msg", "Excel 数据超过60000行,请检查是否有空行,或分批导入");
                return result;
            } else {
                if (null == sheet || sheet.getLastRowNum() == 0) {
                    result.put("status", "0");
                    result.put("msg", "Excel请检查是否有空行");
                    return result;
                }
                Row rowx = sheet.getRow(rowNumIndex[0] - 1);
                for (int i = 0; i < rowx.getLastCellNum(); i++) {
                    String tempCellValue = sheet.getRow(rowNumIndex[0] - 1).getCell(i)
                        .getStringCellValue();
                    tempCellValue = StringUtils.remove(tempCellValue, (char) 160);
                    tempCellValue = tempCellValue.trim();
                    cellList.remove(tempCellValue);
                }
                if (cellList.size() > CommonConstant.ZERO) {
                    result.put("msg", "导入表格格式有误，导入失败。");
                    result.put("status", "0");
                    return result;
                }
                // 循环行Row
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    // 如果传值指定从第几行开始读，就从指定行寻找，否则自动寻找
                    if (rowNumIndex != null && rowNumIndex.length > 0 && rowNumX == -1) {
                        Row row = sheet.getRow(rowNumIndex[0]);
                        if (row == null) {
                            result.put("status", "0");
                            result.put("msg", "指定的行为空，请检查");
                            return result;
                        }
                        rowNum = rowNumIndex[0] - 1;
                    }
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    boolean flag = false;
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        if (row.getCell(i) != null && !("")
                            .equals(row.getCell(i).toString().trim())) {
                            flag = true;
                        }
                    }
                    if (!flag) {
                        continue;
                    }
                    if (rowNumX == -1) {
                        // 循环列Cell
                        for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            if (cell == null) {
                                continue;
                            }
                            String tempCellValue = sheet.getRow(rowNum).getCell(cellNum)
                                .getStringCellValue();
                            tempCellValue = StringUtils.remove(tempCellValue, (char) 160);
                            tempCellValue = tempCellValue.trim();
                            headList.add(tempCellValue);
                            for (Map.Entry<String, String> entry : map.entrySet()) {
                                String key = entry.getKey();
                                String value = entry.getValue();
                                if (StringUtils.isNotBlank(tempCellValue) && StringUtils
                                    .equals(tempCellValue, key)) {
                                    rowNumX = rowNum;
                                    String attr = value;
                                    cellMap.put(attr, cellNum);
                                }
                            }
                            if (rowNumX == -1) {
                                result.put("status", "0");
                                result.put("msg", "没有找到对应的字段或者对应字段行上面含有不为空白的行字段");
                                return result;
                            }
                        }
                    } else {
                        obj = clazz.newInstance();
                        for (Map.Entry<String, String> entry : map.entrySet()) {
                            String key = entry.getKey();
                            String value = entry.getValue();
                            Integer cellNumX = cellMap.get(value);
                            if (cellNumX == null || row.getCell(cellNumX) == null) {
                                continue;
                            }
                            // 得到属性
                            String attr = value;
                            Class[] beanClasses = {obj.getClass()};
                            Class<?> attrType = BeanUtils.findPropertyType(attr, beanClasses);
                            Cell cell = row.getCell(cellNumX);
                            getValue(cell, obj, attr, attrType, rowNum, cellNumX, key);

                        }
                        list.add(obj);
                    }
                }
            }
        }
        is.close();
        wb.close();
        result.put("status", "1");
        result.put("result", list);
        return result;
    }


    /**
     * 获取WorkBook
     */
    @NotNull
    private static Workbook getWorkBook(String fileType, InputStream is) {
        Workbook wb;
        try {
            if (ExcelConstant.XLS.equals(fileType)) {
                wb = new HSSFWorkbook(is);
            } else if (ExcelConstant.XLSX.equals(fileType)) {
                wb = new XSSFWorkbook(is);
            } else {
                throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, "输入的excel格式不正确");
            }
        } catch (IOException e) {
            throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, "获取WorkBook发生异常");
        }
        return wb;
    }

    /**
     * 创建根据数据结构合并的表格
     */
    public static <T> void exportExcelAutoCellRange(OutputStream outStream, List<String> titleRow,
        List<T> list, String fileType) throws Exception {
        //把要展示的内容作为Key，表头title作为value。保存在map中。
        Map<String, String> map = getTitleMap(titleRow);
        //对应字符串依然是key
        List<String> keyList = getTitleKeys(titleRow);
        List<ListTreeKey> treeKeyList = getTreeKey(titleRow);
        Workbook wb = null;
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            sheet.setDefaultColumnWidth(15);
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ZERO);

            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                cell.setCellStyle(style);
                cell.setCellValue(map.get(key));
                index++;
            }
            CellStyle erStyle = createCellStyle(wb, (short) 10, false, true);
            //要创建行的起始位置。
            int nextRowNum = CommonConstant.ONE;
            //记录新的行起始位置
            int newRowStartNum = CommonConstant.ONE;
            //list中元素总数
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                // int rowCount = CommonConstant.ONE;
                //获取元素
                T t = list.get(i);
                //遍历keyTree，根据key把数据放到对应位置。
                for (int j = 0; j < treeKeyList.size(); j++) {
                    ListTreeKey itemTreeKey = treeKeyList.get(j);
                    if (itemTreeKey.getChildKey().size() > 0) {
                        //如果keyTree有子项，获取子项对应的list
                        Object item = getAttrVal(t, itemTreeKey.getKey());
                        List<ListTreeKey> chiledTreeKeys = itemTreeKey.getChildKey();
                        //子项对应的位置起始应该是为1

                        if (item instanceof List) {
                            List itemList = (List) item;
                            for (int k = CommonConstant.ZERO; k < itemList.size(); k++) {
                                int colNum = CommonConstant.ONE;
                                Object obj = itemList.get(k);
                                HSSFRow row = sheet.getRow(nextRowNum);
                                if (row == null) {
                                    row = sheet.createRow(nextRowNum);
                                }
                                for (int l = CommonConstant.ZERO; l < chiledTreeKeys.size(); l++) {

                                    Object valueObj = getAttrVal(obj,
                                        chiledTreeKeys.get(l).getKey());
                                    //  row.createCell(colNum).setCellValue(getObject(valueObj));
                                    HSSFCell cell = row.createCell(colNum);
                                    cell.setCellValue(getObject(valueObj));
                                    cell.setCellStyle(erStyle);
                                    //列自增
                                    colNum++;
                                }
                                //行自增
                                nextRowNum++;
                            }
                        }
                    } else {
                        Object valueObj = getAttrVal(t, itemTreeKey.getKey());
                        HSSFRow row = sheet.getRow(newRowStartNum);
                        if (row == null) {
                            row = sheet.createRow(newRowStartNum);
                        }
                        HSSFCell cell = row.createCell(0);
                        cell.setCellValue(getObject(valueObj));
                        cell.setCellStyle(erStyle);
                    }
                }
                if (newRowStartNum < (nextRowNum - 1)) {
                    CellRangeAddress range = new CellRangeAddress(newRowStartNum, nextRowNum - 1, 0,
                        0);
                    sheet.addMergedRegion(range);
                }
                newRowStartNum = nextRowNum;
            }
            if (null != wb) {
                wb.write(outStream);
            }
        }
    }

    /**
     * 把多级结构的key转换为树形结构
     */
    private static List<ListTreeKey> getTreeKey(List<String> titleRow) {
        List<ListTreeKey> treeKeys = new ArrayList<>();
        for (String keyTitle : titleRow) {
            if (StringUtils.isNotEmpty(keyTitle) && keyTitle.contains(ExcelConstant.COLON)) {
                ListTreeKey treeKey = new ListTreeKey();
                String[] strArray = keyTitle.split(ExcelConstant.COLON);
                if (strArray.length != 2) {
                    throw new BusinessException("数据格式不正确");
                }
                if (strArray[0].contains(ExcelConstant.PERIOD)) {
                    String[] keys = strArray[0].split(String.format("\\%s", ExcelConstant.PERIOD));
                    ListTreeKey listTreeKey = getTreeKeyNode(treeKeys, keys[0]);
                    if (listTreeKey == null) {
                        listTreeKey = new ListTreeKey();
                        treeKeys.add(listTreeKey);
                    }
                    listTreeKey.setKey(keys[0]);

                    ListTreeKey childTreeKey = new ListTreeKey();
                    childTreeKey.setKey(keys[1]);
                    childTreeKey.setKeyName(strArray[1]);
                    listTreeKey.getChildKey().add(childTreeKey);

                } else {
                    treeKey.setKey(strArray[0]);
                    treeKey.setKeyName(strArray[1]);
                    treeKeys.add(treeKey);
                }
            } else {
                throw new BusinessException("数据格式不正确");
            }
        }
        return treeKeys;
    }

    /**
     * 查找树形key
     */
    private static ListTreeKey getTreeKeyNode(List<ListTreeKey> treeKeys, String nodeKey) {
        for (ListTreeKey item : treeKeys) {
            if (nodeKey.equals(item.getKey())) {
                return item;
            }
        }
        return null;
    }

    /**
     * 树形结构的key
     */
    private static class ListTreeKey {

        String key;
        String keyName;
        List<ListTreeKey> childKey = new ArrayList<>();

        public String getKeyName() {
            return keyName;
        }

        public void setKeyName(String keyName) {
            this.keyName = keyName;
        }

        public String getKey() {
            return key;
        }

        public void setKey(String key) {
            this.key = key;
        }

        public List<ListTreeKey> getChildKey() {
            return childKey;
        }

        public void setChildKey(List<ListTreeKey> childKey) {
            this.childKey = childKey;
        }
    }


    /**
     * 获取key对应的title描述，key可能为多级。多级属性用.描述，如：***.**
     */
    private static Map getTitleMap(List<String> titleList) {
        HashMap<String, String> hashMap = new HashMap<>();
        for (String keyTitle : titleList) {
            if (StringUtils.isNotEmpty(keyTitle) && keyTitle.contains(ExcelConstant.COLON)) {
                String[] strArray = keyTitle.split(ExcelConstant.COLON);
                if (strArray.length != 2) {
                    throw new BusinessException("数据格式不正确");
                }
                hashMap.put(strArray[0], strArray[1]);
            } else {
                throw new BusinessException("数据格式不正确");
            }
        }
        return hashMap;
    }

    private static List getTitleKeys(List<String> titleList) {
        List<String> keys = new ArrayList<>();
        for (String keyTitle : titleList) {
            if (StringUtils.isNotEmpty(keyTitle) && keyTitle.contains(ExcelConstant.COLON)) {
                String[] strArray = keyTitle.split(ExcelConstant.COLON);
                if (strArray.length != 2) {
                    throw new BusinessException("数据格式不正确");
                }
                keys.add(strArray[0]);
            } else {
                throw new BusinessException("数据格式不正确");
            }
        }
        return keys;
    }

    public static <T> void exportExcelLqh(OutputStream outStream, String keyValue, List<T> list,
                                       String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int lastCol = map.size() - 1;
//            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
//            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);

            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ZERO);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.ONE;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    /**
     * exportExcel:(导出Excel)
     *
     * @param outStream 输出流t
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型
     * @param fileType 文件类型
     * @param title 表格表头
     */
    public static <T> void exportExcel(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title,List<CellRangeAddressList> addressList,List<String>...tanges) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            DataFormat dataFormat = wb.createDataFormat();
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                //设置为文本,防止科学计数
                style.setDataFormat(dataFormat.getFormat("@"));
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    cellStyle.setDataFormat(dataFormat.getFormat("@"));
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            CellStyle textStyle = wb.createCellStyle();
            DataFormat format = wb.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);

            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                sheet.setDefaultColumnStyle(index, textStyle);
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            for (int ai=0;ai<addressList.size();ai++){
                // 4. 创建数据验证
                DataValidationHelper dvHelper = sheet.getDataValidationHelper();
                DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint( String.join(",",tanges[ai]).split(",") );
                DataValidation validation = dvHelper.createValidation(dvConstraint, addressList.get(ai));

                // 5. 重要设置：显示下拉箭头
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);

                // 6. 将验证添加到工作表
                sheet.addValidationData(validation);
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                //sheet.setDefaultColumnStyle(i, textStyle);
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    //cellStyle.setDataFormat(dataFormat.getFormat("@"));
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    /**
     * exportExcel:(导出Excel)
     *
     * @param outStream 输出流t
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型
     * @param fileType 文件类型
     * @param title 表格表头
     */
    public static <T> void exportExcel(OutputStream outStream, String keyValue, List<T> list,
                                       String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            DataFormat dataFormat = wb.createDataFormat();
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                //设置为文本,防止科学计数
                style.setDataFormat(dataFormat.getFormat("@"));
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    cellStyle.setDataFormat(dataFormat.getFormat("@"));
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            CellStyle textStyle = wb.createCellStyle();
            DataFormat format = wb.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);

            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                sheet.setDefaultColumnStyle(index, textStyle);
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }

            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                //sheet.setDefaultColumnStyle(i, textStyle);
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    //cellStyle.setDataFormat(dataFormat.getFormat("@"));
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }
    
    
    
    
    /**
     * 预导出表格，使用场景：多个sheet
     * 只支持xls 格式
     *
     * @param paramMap 参数map
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型

     */
    public static <T> void preExportExcelOneSheet(Map paramMap, String keyValue, List<T> list) {

        String title = (String) paramMap.get("title");
        String sheetName = (String) paramMap.get("sheetName");
        Workbook wb = (Workbook) paramMap.get("workbook");

        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        // 创建HSSFWorkbook对象(excel的文档对象)
//        wb = new HSSFWorkbook();
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        int lastCol = map.size() - 1;
        CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
        // 建立新的sheet对象（excel的表单）
        HSSFSheet sheet = (HSSFSheet) wb.createSheet(sheetName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        sheet.addMergedRegion(callRangeAddress);
        // 声明样式
        HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
        hssfCellStyleForXls(wb, style);
        //表格标题
        HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
        HSSFCell titleCell = rowTitle.createCell(0);
        rowTitle.setHeight((short) 600);
        //加载单元格样式
        CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
        titleCell.setCellStyle(erStyle);
        titleCell.setCellValue(title);
        //标题换行设置
        erStyle.setWrapText(true);
        titleCell.setCellValue(new HSSFRichTextString(title));
        // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
        for (String key : keyList) {
            HSSFCell cell = rowHeader.createCell(index);
            style.setWrapText(true);
            cell.setCellStyle(style);
            cell.setCellValue(key);
            attMap.put(Integer.toString(index), map.get(key));
            index++;
        }
        // 在sheet里创建表头下的数据
        for (int i = CommonConstant.ZERO; i < list.size(); i++) {
            int rowNum = i + CommonConstant.TWO;
            HSSFRow row = sheet.createRow(rowNum);
            for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                String key = Integer.toString(j);
                String att = attMap.get(key);
                T t = list.get(i);
                Object value = getAttrVal(t, att);
                cellStyle.setWrapText(true);
                row.createCell(j).setCellStyle(cellStyle);
                row.createCell(j).setCellValue(getObject(value));
            }
        }
    }


    /**
     * exportBankExcel:(导出Excel)
     *
     * @param outStream 输出流t
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型
     * @param fileType 文件类型
     * @param title 表格表头
     */
    public static <T> void exportBankExcel(OutputStream outStream, String keyValue, List<T> list,
                                       String fileType, String title, List<String> stringList) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
            HSSFRow rowHeaderOne = sheet.createRow(CommonConstant.ONE);
            for (int i = 0; i < stringList.size(); i++) {
                HSSFCell cell = rowHeaderOne.createCell(i);
                style.setWrapText(true);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(stringList.get(i));
                attMap.put(Integer.toString(index), map.get(stringList.get(i)));
            }
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.TWO);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.THREE;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }


    public static <T> void exportBankExcelV2(OutputStream outStream, String keyValue, List<T> list,
                                           String fileType, String title, List<String> stringList) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
           /* HSSFRow rowHeaderOne = sheet.createRow(CommonConstant.ONE);
            for (int i = 0; i < stringList.size(); i++) {
                HSSFCell cell = rowHeaderOne.createCell(i);
                style.setWrapText(true);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(stringList.get(i));
                attMap.put(Integer.toString(index), map.get(stringList.get(i)));
            }*/
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            /*XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }*/
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }


    public static <T> void exportHistoryExcel(OutputStream outStream, String keyValue, List<T> list,
                                           String fileType, String title, List<String> stringList) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
//            HSSFRow rowHeaderOne = sheet.createRow(CommonConstant.ONE);
//            for (int i = 0; i < stringList.size(); i++) {
//                HSSFCell cell = rowHeaderOne.createCell(i);
//                style.setWrapText(true);
//                cell.setCellStyle(cellStyle);
//                cell.setCellValue(stringList.get(i));
//                attMap.put(Integer.toString(index), map.get(stringList.get(i)));
//            }
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    /**
     * exportExcel:(导出Excel)
     *
     * @param outStream 输出流
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型
     * @param fileType 文件类型
     * @param title 表格表头
     * @param descList 查看注意事项
     */
    public static <T> void exportExcelV2(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title, List<String> descList) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
            //创建第一行描述语
//            HSSFRow rowDescription = sheet.createRow(CommonConstant.ONE);
//            HSSFCell cellDescription = rowDescription.createCell(CommonConstant.ZERO);
//            HSSFCellStyle styleDescription = (HSSFCellStyle) wb.createCellStyle();
//            // 生成一个字体
//            Font font = wb.createFont();
//            font.setFontName("宋体");
//            font.setBold(true);
//            font.setFontHeightInPoints((short) 15);
//            // 把字体应用到当前的样式
//            styleDescription.setFont(font);
//            // 设置这些样式
////            styleDescription.setFillPattern(HSSFFillPatternType.SOLID_FOREGROUND);
//            cellDescription.setCellStyle(styleDescription);
//            cellDescription.setCellValue("请点击本表左下角“导入说明页签”，查看注意事项。");
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
//            // 建立新的sheet对象（excel的表单）
//            HSSFSheet sheet2 = (HSSFSheet) wb.createSheet("导入说明页签");
//            // 设置表格默认列宽度为15个字节
//            sheet2.setDefaultColumnWidth(20);
//            sheet2.addMergedRegion(callRangeAddress);
            // 声明样式
//            HSSFCellStyle style2 = (HSSFCellStyle) wb.createCellStyle();
//            hssfCellStyleForXls(wb, style2);
            //表格标题
//            HSSFRow rowTitle2 = sheet2.createRow(CommonConstant.ZERO);
//            HSSFCell titleCell2 = rowTitle2.createCell(0);
//            rowTitle2.setHeight((short) 600);
            //加载单元格样式
//            CellStyle erStyle2 = createCellStyle(wb, (short) 13, true, true);
//            titleCell2.setCellStyle(erStyle2);
//            titleCell2.setCellValue(title);
//            //标题换行设置
//            erStyle2.setWrapText(true);
//            titleCell2.setCellValue(new HSSFRichTextString(title));

            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader2 =
                sheet.getRow(CommonConstant.ONE) != null ? sheet.getRow(CommonConstant.ONE)
                    : sheet.createRow(CommonConstant.ONE);
            HSSFCell cell = rowHeader2.createCell(map.size());
            cell.setCellStyle(style);
            cell.setCellValue("导入说明具体内容如下:");
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < descList.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row =
                    sheet.getRow(rowNum) != null ? sheet.getRow(rowNum) : sheet.createRow(rowNum);
                row.createCell(map.size()).setCellValue(descList.get(i));
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
//            // 建立新的sheet对象（excel的表单）
//            XSSFSheet sheet2 = (XSSFSheet) wb.createSheet("导入说明页签");
//            // 设置表格默认列宽度为15个字节
//            sheet2.setDefaultColumnWidth(20);
//            sheet2.addMergedRegion(callRangeAddress);
//            // 声明样式
//            XSSFCellStyle style2 = (XSSFCellStyle) wb.createCellStyle();
//            xssfCellStyleFor2007(wb, style2);
//            //标题
//            XSSFRow rowTitle2 = sheet.createRow(CommonConstant.ZERO);
//            rowTitle2.setHeight((short) 600);
//            //表格标题
//            XSSFCell titleCell2 = rowTitle2.createCell(0);
//            //加载单元格样式
//            CellStyle erStyle2 = createCellStyle(wb, (short) 13, true, true);
//            titleCell2.setCellStyle(erStyle);
//            titleCell2.setCellValue(title);
//            //标题换行设置
//            erStyle2.setWrapText(true);
//            titleCell2.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader2 =
                sheet.getRow(CommonConstant.ONE) != null ? sheet.getRow(CommonConstant.ONE)
                    : sheet.createRow(CommonConstant.ONE);
            XSSFCell cell = rowHeader2.createCell(map.size());
            cell.setCellStyle(style);
            cell.setCellValue("导入说明具体内容如下:");
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < descList.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row =
                    sheet.getRow(rowNum) != null ? sheet.getRow(rowNum) : sheet.createRow(rowNum);
                row.createCell(map.size()).setCellValue(descList.get(i));
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    /**
     * exportExcel:(导出Excel)
     *
     * @param outStream 输出流
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型
     * @param fileType 文件类型
     * @param title 表格表头
     */
    public static <T> void exportExcelV3(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            //垂直居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));

            // 在sheet里创建第二行为注解，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            for (int i = CommonConstant.ZERO; i < 5; i++) {
                int rowNum = i + CommonConstant.ONE;
                HSSFRow row = sheet.createRow(rowNum);
                row.createCell(0).setCellValue(getCoceralDescription().get(i));
            }

            //合并单元格
            //合并单元格，cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列
            for (int i = 0; i < keyList.size(); i++) {
                if (i <= 10 || i == 15) {
                    sheet.addMergedRegion(new CellRangeAddress(6, 8, i, i));
                }
            }
            // 在sheet里创建第七行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader2 = sheet.createRow(CommonConstant.SIX);
            for (int i = 0; i <= 10; i++) {
                String key = keyList.get(index);
                HSSFCell cell3 = rowHeader2.createCell(i);
                cell3.setCellStyle(style);
                cell3.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }

            HSSFCell cell1 = rowHeader2.createCell(11);
            cell1.setCellStyle(style);
            cell1.setCellValue("分类别审核");
            sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 14));

            for (int i = 15; i <= 15; i++) {
                String key = keyList.get(15);
                HSSFCell cell3 = rowHeader2.createCell(i);
                cell3.setCellStyle(style);
                cell3.setCellValue(key);
                attMap.put(Integer.toString(15), map.get(key));
            }

            HSSFRow rowHeader1 = sheet.createRow(CommonConstant.SEVEN);
            HSSFCell cell2 = rowHeader1.createCell(11);
            cell2.setCellStyle(style);
            cell2.setCellValue("A-C项默认显示交易团审核结果,D项默认显示'合格',商协会均可修改");
            sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 14));

            // 在sheet里创建第八行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader3 = sheet.createRow(CommonConstant.EIGHT);

            for (int i = 11; i < 15; i++) {
                String key = keyList.get(i);
                HSSFCell cell3 = rowHeader3.createCell(i);
                cell3.setCellStyle(style);
                cell3.setCellValue(key);
                attMap.put(Integer.toString(i), map.get(key));
                index++;
            }

            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + 9;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    /**
     * exportExcel:(导出Excel)
     *
     * @param outStream 输出流
     * @param keyValue 表头和属性
     * @param list 集合<T> 必须定义类型
     * @param fileType 文件类型
     * @param title 表格表头
     */
    public static <T> void exportExcelV5(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            sheet.protectSheet("123456");
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            //垂直居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));

            //将最后两列隐藏
            sheet.setColumnHidden((short) 29, true);
            sheet.setColumnHidden((short) 30, true);

            //合并单元格
            //合并单元格，cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列
            for (int i = 0; i < keyList.size(); i++) {
                if (i == 11 || i >= 26) {
                    sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
                }
            }

            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            for (int i = 0; i < keyList.size(); i++) {
                if (i == 11 || i >= 26) {
                    String key = keyList.get(i);
                    HSSFCell cell3 = rowHeader.createCell(i);
                    cell3.setCellStyle(style);
                    cell3.setCellValue(key);
                    attMap.put(Integer.toString(i), map.get(key));
                }
            }

            HSSFCell cell1 = rowHeader.createCell(0);
            cell1.setCellStyle(style);
            cell1.setCellValue("企业基本信息");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 10));

            HSSFCell cell2 = rowHeader.createCell(12);
            cell2.setCellStyle(style);
            cell2.setCellValue("出口额");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 13));

            HSSFCell cell3 = rowHeader.createCell(14);
            cell3.setCellStyle(style);
            cell3.setCellValue("品牌建设");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 17));

            HSSFCell cell4 = rowHeader.createCell(18);
            cell4.setCellStyle(style);
            cell4.setCellValue("研发创新和自主知识产权");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 18, 20));

            HSSFCell cell5 = rowHeader.createCell(21);
            cell5.setCellStyle(style);
            cell5.setCellValue("国际通行认证");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 21, 23));

            HSSFCell cell6 = rowHeader.createCell(24);
            cell6.setCellStyle(style);
            cell6.setCellValue("境内外商标");
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 24, 25));

            HSSFRow rowHeader1 = sheet.createRow(CommonConstant.TWO);

            for (int i = 0; i < keyList.size(); i++) {
                String key = keyList.get(i);
                HSSFCell cell12 = rowHeader1.createCell(i);
                cell12.setCellStyle(style);
                cell12.setCellValue(key);
                attMap.put(Integer.toString(i), map.get(key));
            }

            HSSFCellStyle lockStyle = (HSSFCellStyle) wb.createCellStyle();
            //垂直居中
            lockStyle.setLocked(true);
            lockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            lockStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

            HSSFCellStyle unLockStyle = (HSSFCellStyle) wb.createCellStyle();
            //垂直居中
            unLockStyle.setLocked(false);//设置锁定
            unLockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            unLockStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.THREE;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    HSSFCell cell = row.createCell(j);
                    if (j >= 13 && j <= 26 && j != 14 && j != 16) {
                        cell.setCellStyle(unLockStyle);
                    } else {
                        cell.setCellStyle(lockStyle);
                    }
                    cell.setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }


    public static <T> void exportExcelV4(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            //垂直居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));

            // 在sheet里创建第二行为注解，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            for (int i = CommonConstant.ZERO; i < 5; i++) {
                int rowNum = i + CommonConstant.ONE;
                HSSFRow row = sheet.createRow(rowNum);
                row.createCell(0).setCellValue(getCoceralDescription().get(i));
            }

            //合并单元格
            //合并单元格，cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列
            for (int i = 0; i < keyList.size(); i++) {
                if (i <= 11 || i == 16) {
                    sheet.addMergedRegion(new CellRangeAddress(6, 8, i, i));
                }
            }
            // 在sheet里创建第七行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader2 = sheet.createRow(CommonConstant.SIX);
            for (int i = 0; i <= 11; i++) {
                String key = keyList.get(index);
                HSSFCell cell3 = rowHeader2.createCell(i);
                cell3.setCellStyle(style);
                cell3.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }

            HSSFCell cell1 = rowHeader2.createCell(12);
            cell1.setCellStyle(style);
            cell1.setCellValue("分类别审核");
            sheet.addMergedRegion(new CellRangeAddress(6, 6, 12, 15));

            for (int i = 16; i <= 16; i++) {
                String key = keyList.get(16);
                HSSFCell cell3 = rowHeader2.createCell(i);
                cell3.setCellStyle(style);
                cell3.setCellValue(key);
                attMap.put(Integer.toString(16), map.get(key));
            }

            HSSFRow rowHeader1 = sheet.createRow(CommonConstant.SEVEN);
            HSSFCell cell2 = rowHeader1.createCell(12);
            cell2.setCellStyle(style);
            cell2.setCellValue("A-C项默认显示交易团审核结果,D项默认显示'合格',商协会均可修改");
            sheet.addMergedRegion(new CellRangeAddress(7, 7, 12, 15));

            // 在sheet里创建第八行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader3 = sheet.createRow(CommonConstant.EIGHT);

            for (int i = 12; i < 16; i++) {
                String key = keyList.get(i);
                HSSFCell cell3 = rowHeader3.createCell(i);
                cell3.setCellStyle(style);
                cell3.setCellValue(key);
                attMap.put(Integer.toString(i), map.get(key));
                index++;
            }

            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + 9;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    @NotNull
    public static String getObject(Object value) {
        if (null == value) {
            value = "";
        }
        return String.valueOf(value);
    }

    public static <T> void exportExcelV6(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);

            //将最后两列隐藏
            sheet.setColumnHidden((short) 19, true);
            sheet.setColumnHidden((short) 20, true);

            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }

    public static <T> void exportExcelV7(OutputStream outStream, String keyValue, List<T> list,
        String fileType, String title) throws Exception {
        Map<String, String> map = getMap(keyValue);
        List<String> keyList = getList(keyValue);
        Workbook wb = null;
        // 存储属性信息
        Map<String, String> attMap = new HashMap(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        int index = CommonConstant.ZERO;
        if (ExcelConstant.XLS.equals(fileType)) {
            // 创建HSSFWorkbook对象(excel的文档对象)
            wb = new HSSFWorkbook();
            CellStyle cellStyle = wb.createCellStyle();
            int lastCol = keyList.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = (HSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
            hssfCellStyleForXls(wb, style);
            //表格标题
            HSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            HSSFCell titleCell = rowTitle.createCell(0);
            rowTitle.setHeight((short) 600);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);

            //将最后一列隐藏
            sheet.setColumnHidden((short) 39, true);

            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new HSSFRichTextString(title));
            // 在sheet里创建第二行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            for (String key : keyList) {
                HSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    String att = attMap.get(key);
                    T t = list.get(i);
                    Object value = getAttrVal(t, att);
                    cellStyle.setWrapText(true);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        } else if (ExcelConstant.XLSX.equals(fileType)) {
            wb = new XSSFWorkbook();
            int lastCol = map.size() - 1;
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
            // 建立新的sheet对象（excel的表单）
            XSSFSheet sheet = (XSSFSheet) wb.createSheet(ExcelConstant.SHEET_NAME);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(20);
            sheet.addMergedRegion(callRangeAddress);
            // 声明样式
            XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
            xssfCellStyleFor2007(wb, style);
            //标题
            XSSFRow rowTitle = sheet.createRow(CommonConstant.ZERO);
            rowTitle.setHeight((short) 600);
            //表格标题
            XSSFCell titleCell = rowTitle.createCell(0);
            //加载单元格样式
            CellStyle erStyle = createCellStyle(wb, (short) 13, true, true);
            titleCell.setCellStyle(erStyle);
            titleCell.setCellValue(title);
            //标题换行设置
            erStyle.setWrapText(true);
            titleCell.setCellValue(new XSSFRichTextString(title));

            // 在sheet里创建第一行为表头，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow rowHeader = sheet.createRow(CommonConstant.ONE);
            // 创建单元格并设置单元格内容
            for (String key : keyList) {
                XSSFCell cell = rowHeader.createCell(index);
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }
            // 在sheet里创建表头下的数据
            for (int i = CommonConstant.ZERO; i < list.size(); i++) {
                int rowNum = i + CommonConstant.TWO;
                XSSFRow row = sheet.createRow(rowNum);
                for (int j = CommonConstant.ZERO; j < map.size(); j++) {
                    String key = Integer.toString(j);
                    T t = list.get(i);
                    Object value = getAttrVal(t, attMap.get(key));
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setWrapText(true);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 下边框
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    // 左边框
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    // 上边框
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    // 右边框
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    Font cellfont = wb.createFont();
                    cellfont.setFontHeightInPoints((short) 12);
                    cellfont.setFontName("宋体");
                    cellStyle.setFont(cellfont);
                    row.createCell(j).setCellStyle(cellStyle);
                    row.createCell(j).setCellValue(getObject(value));
                }
            }
        }
        if (null != wb) {
            wb.write(outStream);
        }
    }


    /**
     * 表格标题的样式
     *
     * @return 单元格样式
     */
    public static CellStyle createCellStyle(Workbook workbook, short fontsize, boolean fontBold,
        boolean alignCenter) {
        CellStyle style = workbook.createCellStyle();
        //是否水平居中
        if (alignCenter) {
            //水平居中
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建字体
        Font font = workbook.createFont();
        //是否加粗字体
        if (fontBold) {
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }

    /**
     * 设置样式
     */
    public static void xssfCellStyleFor2007(Workbook wb, XSSFCellStyle style) {
        // 设置这些样式
        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(new XSSFColor(java.awt.Color.gray));
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 生成一个字体
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(new XSSFColor(java.awt.Color.BLACK).getIndex());
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
    }

    /**
     * 设置样式
     */
    public static void hssfCellStyleForXls(Workbook wb, HSSFCellStyle style) {
        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置这些样式
        style.setFillForegroundColor(HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 生成一个字体
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(HSSFColorPredefined.WHITE.getIndex());
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
    }

    /**
     * getMap:(将传进来的表头和表头对应的属性存进Map集合，表头字段为key,属性为value)
     *
     * @param keyValue 把传进指定格式的字符串解析到Map中 形如: String keyValue = "手机名称:phoneName,颜色:color,售价:price";
     */
    public static Map<String, String> getMap(String keyValue) {
        Map<String, String> map = new HashMap<>(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        if (keyValue != null) {
            String[] str = keyValue.split(ExcelConstant.COMMA);
            for (String element : str) {
                //截取最后一个冒号之前作为key，之后作为value
                int index = element.lastIndexOf(ExcelConstant.COLON);
                String key = element.substring(CommonConstant.ZERO, index);
                String value = element.substring(index + CommonConstant.ONE);
                map.put(key, value);
//                String[] str2 = element.split(ExcelConstant.COLON);
//                map.put(str2[0], str2[1]);
            }
        }
        return map;
    }

    public static void main(String[] args) {
       String element = "00:00-01:00:zeroData";
        int index = element.lastIndexOf(ExcelConstant.COLON);
        String key = element.substring(CommonConstant.ZERO, index);
        String value = element.substring(index + CommonConstant.ONE);
        System.out.println(key);
        System.out.println(value);
    }

    /**
     * @param keyValue 把传进指定格式的字符串解析到List中
     * @return List
     * @since JDK 1.7
     */
    public static List<String> getList(String keyValue) {
        List<String> list = new ArrayList<>();
        if (null != keyValue) {
            String regex = ExcelConstant.COMMA;
            String[] str = keyValue.split(regex);
            for (String element : str) {
                //截取最后一个冒号之前作为key，之后作为value
                int index = element.lastIndexOf(ExcelConstant.COLON);
                String key = element.substring(CommonConstant.ZERO, index);
                list.add(key);
//                String[] str2 = element.split(ExcelConstant.COLON);
//                list.add(str2[0]);
            }
        }
        return list;
    }

    /**
     * getMap:(将传进来的表头和表头对应的属性存进Map集合，属性为key, 表头字段为value)
     *
     * @param keyValue : keyValue
     * @return : java.util.Map<java.lang.String,java.lang.String>
     * @author cqchen
     **/
    public static Map<String, String> getMap2(String keyValue) {
        Map<String, String> map = new HashMap<>(CommonConstant.MAP_DEFAULT_INITIAL_CAPACITY);
        if (keyValue != null) {
            String[] str = keyValue.split(ExcelConstant.COMMA);
            for (String element : str) {
                String[] str2 = element.split(ExcelConstant.COLON);
                map.put(str2[1], str2[0]);
            }
        }
        return map;
    }

    /**
     * @param keyValue 把传进指定格式的字符串key解析到List中,属性
     * @return List
     * @since JDK 1.7
     */
    public static List<String> getList2(String keyValue) {
        List<String> list = new ArrayList<>();
        if (null != keyValue) {
            String regex = ExcelConstant.COMMA;
            String[] str = keyValue.split(regex);
            for (String element : str) {
                String[] str2 = element.split(ExcelConstant.COLON);
                list.add(str2[1]);
            }
        }
        return list;
    }

    /**
     * 设置样式(白底黑字)
     *
     * @param wb wb
     * @param style style
     */
    public static void hssfCellStyleBlackForXls(Workbook wb, HSSFCellStyle style) {
        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置这些样式
        style.setFillForegroundColor(HSSFColorPredefined.AUTOMATIC.getIndex());
        style.setFillPattern(FillPatternType.NO_FILL);
        // 生成一个字体
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor(HSSFColorPredefined.BLACK.getIndex());
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        style.setFont(font);
    }

    /**
     * getAttrVal:(反射的get方法得到属性值)
     *
     * @param obj 具体的类
     * @param att 类的属性
     * @return value 赋予属性的值
     */
    public static Object getAttrVal(Object obj, String att) {
        try {
            //如果是 hashMap 类型直接get
            if (obj instanceof HashMap) {
                return obj.getClass().getMethod("get", Object.class).invoke(obj, att);
            }
            Method method = obj.getClass().getMethod("get" + StringUtil.toUpperCaseFirstOne(att));
            Object value = method.invoke(obj);

            if(value instanceof Date){
                value = com.easylinkin.linkappapi.common.utils.DateUtil.format((Date)value, com.easylinkin.linkappapi.common.utils.DateUtil.DATE_TIME_FORMAT_DEFAULT);
            }
            return value;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            return null;
        }
    }

    /**
     * setter:(反射的set方法给属性赋值)
     *
     * @param obj 具体的类
     * @param att 类的属性
     * @param value 赋予属性的值
     * @param type 属性是哪种类型 比如:String double boolean等类型
     * @author likaixuan
     * @since JDK 1.7
     */
    public static void setter(Object obj, String att, Object value, Class<?> type, int row, int col,
        Object key) {
        //为空的情况不设置对象的属性值
        if (null == value || "".equals(value)) {
            return;
        }
        try {
            Method method = obj.getClass()
                .getMethod("set" + StringUtil.toUpperCaseFirstOne(att), type);
            if (type == String.class) {
                method.invoke(obj, value);
            } else if (type == BigDecimal.class) {
                method.invoke(obj, getBigDecimal(value));
            } else {
                String s = StringHelper.toStr(value);
                if (type == long.class) {
                    method.invoke(obj, Long.valueOf(s));
                } else if (type == Double.class) {
                    method.invoke(obj, Double.valueOf(s));
                } else if (type == Float.class) {
                    method.invoke(obj, Float.valueOf(s));
                } else if (type == int.class || type == Integer.class) {
                    method.invoke(obj, Integer.valueOf(s));
                } else if (type == Short.class) {
                    method.invoke(obj, Short.valueOf(s));
                } else if (type == Date.class) {
                    Date dateValue = dateConvertFormat(s);
                    method.invoke(obj, dateValue);
                } else {
                    method.invoke(obj, value);
                }
            }
        } catch (Exception e) {
            String message = "第" + (row + 1) + " 行  " + (col + 1) + "列   属性：" + key + " 赋值异常  " + e;
            throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, message);
        }

    }

    public static BigDecimal getBigDecimal(Object value) {
        BigDecimal ret = null;
        if (value != null) {
            if (value instanceof BigDecimal) {
                ret = (BigDecimal) value;
            } else if (value instanceof String) {
                ret = new BigDecimal((String) value);
            } else if (value instanceof BigInteger) {
                ret = new BigDecimal((BigInteger) value);
            } else if (value instanceof Number) {
                ret = BigDecimal.valueOf(((Number) value).doubleValue());
            } else {
                throw new ClassCastException(
                    "Not possible to coerce [" + value + "] from class " + value.getClass()
                        + " into a BigDecimal.");
            }
        }
        return ret;
    }

    /**
     * getValue:(得到Excel列的值)
     *
     * @author likaixuan
     * @since JDK 1.7
     */
    public static void getValue(Cell cell, Object obj, String attr, Class attrType, int row,
        int col, Object key) {
        Object val = null;
        if (cell.getCellType() == CellType.BOOLEAN) {
            val = cell.getBooleanCellValue();

        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    if (attrType == String.class) {
                        val = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else {
                        val = dateConvertFormat(
                            sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())));
                    }
                } catch (ParseException e) {
                    String message =
                        "第" + (row + 1) + " 行  " + (col + 1) + "列   属性：" + key + " 日期格式转换错误  ";
                    throw new BusinessException(CommonExceptionEnum.FILE_READ_ERROR, message);
                }
            } else {
                if (attrType == String.class) {
                    cell.setCellType(CellType.STRING);
                    val = cell.getStringCellValue();
                } else if (attrType == BigDecimal.class) {
                    val = BigDecimal.valueOf(cell.getNumericCellValue());
                } else if (attrType == long.class) {
                    val = (long) cell.getNumericCellValue();
                } else if (attrType == Double.class) {
                    val = cell.getNumericCellValue();
                } else if (attrType == Float.class) {
                    val = (float) cell.getNumericCellValue();
                } else if (attrType == int.class || attrType == Integer.class) {
                    val = (int) cell.getNumericCellValue();
                } else if (attrType == Short.class) {
                    val = (short) cell.getNumericCellValue();
                } else {
                    val = cell.getNumericCellValue();
                }
            }

        } else if (cell.getCellType() == CellType.STRING) {
            val = cell.getStringCellValue();
        }

        setter(obj, attr, val, attrType, row, col, key);
    }

    /**
     * String类型日期转为Date类型
     */
    public static Date dateConvertFormat(String dateStr) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = format.parse(dateStr);
        return date;
    }


    private static List<String> getDescription() {
        List<String> list = new ArrayList<>();
        list.add("A．导入表格格式应与下载表格格式一致，如表格格式有误整体方案将无法导入。");
        list.add("B．请勿添加未通过展位申请信息审核的企业展位数量安排方案，否则整体方案将无法导入。");
        list.add("C．企业展位数量安排不应超过交易团对应展区一般性展位核定数与中央通道展位核定数，否则整体方案将无法导入。");
        list.add("D．数量安排方案不触及企业涉及的黑名单处罚。");
        list.add("E．大型机械及设备展区展位安排数只能在“统一布展推荐数”列下填写（中央通道展位除外）。");
        list.add("F．工程农机（室内/室外）、车辆展区不能安排标摊展位。");
        list.add("G．对应展区没有品牌展位不能安排粘连展位。");
        list.add("H．粘连展位数不能大于特装展位数（大型机械设备展区粘连展位数不能大于统一布展展位数）。");
        list.add("I．贫困地区特色产品展区数量安排方案需填写产品类别和所属贫困县，请以在线输入方式提交安排方案。");
        list.add("J．铁石装饰品及户外水疗展区基数内展位仅能安排在铁石装饰品专区，铁石装饰品专区仅能安排绿色特装与标摊展位" +
            "，户外水疗专区仅能安排绿色特装与统一布展展位。");
        return list;
    }


    private static List<String> getCoceralDescription() {
        List<String> list = new ArrayList<>();
        list.add("注：以下分类别审核中A-D类分别代表：");
        list.add("A类：企业基本信息（包括企业信用环保、违规违法情况，参考《中国进出口商品交易会出口展一般性展位数量安排办法》第二章第十二条）\n");
        list.add("B类：企业进出口经营权。");
        list.add("C类：企业广交会统计口径下上年度出口额是否达到最低参展标准");
        list.add("D类：企业展品情况（参考《中国进出口商品交易会出口展一般性展位数量安排办法》第二章第十一条）");
        return list;
    }

}

